require('dotenv').config();
const { pool } = require('../config/database');

async function run() {
  const connection = await pool.getConnection();
  try {
    console.log('🏗 迁移开始：保留规格并解除级联删除...');
    await connection.beginTransaction();

    // 1) 将 goods_properties.goods_id 改为可为空
    console.log('1) 修改 goods_properties.goods_id 允许为 NULL');
    await connection.execute(`
      ALTER TABLE goods_properties 
      MODIFY COLUMN goods_id INT NULL COMMENT '商品ID（可为空，商品删除后保留规格）'
    `);

    // 2) 删除旧的外键约束（名称可能不同，需要查询后动态删除）
    console.log('2) 删除旧的外键约束（如果存在）');
    const [fkRows] = await connection.execute(`
      SELECT CONSTRAINT_NAME 
      FROM information_schema.KEY_COLUMN_USAGE 
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'goods_properties'
        AND REFERENCED_TABLE_NAME = 'goods'
        AND COLUMN_NAME = 'goods_id'
    `);

    for (const row of fkRows) {
      const constraint = row.CONSTRAINT_NAME;
      if (constraint) {
        console.log(`   - 删除外键: ${constraint}`);
        await connection.execute(`ALTER TABLE goods_properties DROP FOREIGN KEY \`${constraint}\``);
      }
    }

    // 3) 添加新的外键 ON DELETE SET NULL
    console.log('3) 添加新的外键 ON DELETE SET NULL');
    await connection.execute(`
      ALTER TABLE goods_properties
      ADD CONSTRAINT fk_goods_properties_goods
      FOREIGN KEY (goods_id) REFERENCES goods(id)
      ON DELETE SET NULL
    `);

    await connection.commit();
    console.log('✅ 迁移完成');
    process.exit(0);
  } catch (e) {
    await connection.rollback();
    console.error('❌ 迁移失败:', e.message);
    process.exit(1);
  } finally {
    connection.release();
  }
}

run();


